package com.bjy.qa.dao.functionaltest;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.bjy.qa.entity.functionaltest.Catalog;
import com.bjy.qa.entity.functionaltest.FullTree;
import com.bjy.qa.entity.functionaltest.Tree;
import com.bjy.qa.enumtype.CatalogType;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Repository;

import java.util.List;

@Mapper
@Repository
public interface CatalogDao extends BaseMapper<Catalog> {
    /**
     * 查询分类列表
     * @param type 分类类型
     * @param projectId 项目ID
     * @param parentId 父分类ID
     * @return 列表
     */
    @Select("SELECT c.* FROM ft_catalog AS c WHERE c.type = #{type} AND c.project_id = #{projectId} AND c.parent_id = #{parentId} AND is_delete=false ORDER BY sort ASC")
    List<Catalog> listCatalogByParentId(@Param("type") CatalogType type, @Param("projectId") Long projectId, @Param("parentId") Long parentId);

    /**
     * 根据分类名称查询分类
     * @param type 分类类型
     * @param projectId 项目ID
     * @param parentId  父分类ID，传入 null 表示不限定父分类只匹配分类名称
     * @param name 分类名称
     * @return
     */
    @Select("SELECT c.* FROM ft_catalog AS c WHERE c.type = #{type} AND c.project_id = #{projectId} AND c.parent_id = #{parentId} AND name = #{name} AND is_delete=false LIMIT 1")
    Catalog selectByName(@Param("type") CatalogType type, @Param("projectId") Long projectId, @Param("parentId") Long parentId, @Param("name") String name);

    /**
     * 查询 TestCase tree 上某个子节点的所有数据
     * @param catalogType 分类的type，test_case-用例、test_suite-套件 他两分类 type 都是 CatalogType.TEST_CASE
     * @param elementType 元素的type
     * @param projectId 项目ID
     * @param parentId tree 节点 ID
     * @return
     */
    @Select("SELECT c.id, c.project_id, c.parent_id, c.type, c.name AS name, true as isCatalog, CONCAT( 'c-', c.id ) `key`, null as priority, null as edit_status, c.sort, c.created_at FROM ft_catalog AS c WHERE c.type = #{cType} AND c.project_id = #{projectId} AND c.parent_id = #{parentId} AND c.is_delete = FALSE " +
            "UNION " +
            "SELECT e.id, e.project_id, e.catalog_id AS parent_id, e.type, e.name AS name, false as isCatalog, CONCAT( 'e-', e.id ) `key`, e.priority, e.edit_status, e.sort, e.created_at FROM ft_test_case AS e WHERE e.type = #{eType} AND e.project_id = #{projectId} AND e.catalog_id = #{parentId} AND e.is_delete = FALSE " +
            "ORDER BY isCatalog DESC, sort ASC, created_at ASC")
    List<Tree> listTestCaseTreeContent(@Param("cType") CatalogType catalogType, @Param("eType") CatalogType elementType, @Param("projectId") Long projectId, @Param("parentId") Long parentId);

    /**
     * 查询 TestScript tree 上某个子节点的所有数据
     * @param type CatalogType（分类的 type）
     * @param projectId 项目ID
     * @param parentId tree 节点 ID
     * @return
     */
    @Select("SELECT c.id, c.project_id, c.parent_id, c.type, c.name AS name, true as isCatalog, CONCAT( 'c-', c.id ) `key`, null as priority, null as edit_status, c.sort, c.created_at FROM ft_catalog AS c WHERE c.type = #{type} AND c.project_id = #{projectId} AND c.parent_id = #{parentId} AND c.is_delete = FALSE " +
            "UNION " +
            "SELECT e.id, e.project_id, e.catalog_id AS parent_id, e.type, e.name AS name, false as isCatalog, CONCAT( 'e-', e.id ) `key`, e.priority, e.edit_status, e.sort, e.created_at FROM pt_test_script AS e WHERE e.type = #{type} AND e.project_id = #{projectId} AND e.catalog_id = #{parentId} AND e.is_delete = FALSE " +
            "ORDER BY isCatalog DESC, sort ASC, created_at ASC")
    List<Tree> listTestScriptTreeContent(@Param("type") CatalogType type, @Param("projectId") Long projectId, @Param("parentId") Long parentId);

    /**
     * 查询 Api tree 上某个子节点的所有数据
     * @param catalogType 分类的type，test_case-用例、test_suite-套件 他两分类 type 都是 CatalogType.TEST_CASE
     * @param elementType 元素的type
     * @param projectId 项目ID
     * @param parentId tree 节点 ID
     * @return
     */
    @Select("SELECT c.id, c.project_id, c.parent_id, c.type, c.name AS name, true as isCatalog, CONCAT( 'c-', c.id ) `key`, c.sort, c.created_at FROM ft_catalog AS c WHERE c.type = #{cType} AND c.project_id = #{projectId} AND c.parent_id = #{parentId} AND c.is_delete = FALSE " +
            "UNION " +
            "SELECT e.id, e.project_id, e.catalog_id AS parent_id, e.type, e.name AS name, false as isCatalog, CONCAT( 'e-', e.id ) `key`, e.sort, e.created_at FROM ft_api AS e WHERE e.type = #{eType} AND e.project_id = #{projectId} AND e.catalog_id = #{parentId} AND e.is_delete = FALSE " +
            "ORDER BY isCatalog DESC, sort ASC, created_at ASC")
    List<Tree> listApiTreeContent(@Param("cType") CatalogType catalogType, @Param("eType") CatalogType elementType, @Param("projectId") Long projectId, @Param("parentId") Long parentId);

    /**
     * 查询某个type 下的 TestScript tree 的所有数据
     * @param type 分类的type，test_case-用例、test_suite-套件 他两分类 type 都是 CatalogType.TEST_CASE
     * @param projectId 项目ID
     * @param parentId tree 节点 ID
     * @return
     */
    @Select("<script>" +
            "SELECT c.id, c.project_id, c.parent_id, c.type, c.name AS name, true as isCatalog, CONCAT( 'c-', c.id ) `key`, null as priority, null as edit_status, c.sort, c.created_at FROM ft_catalog AS c WHERE c.type = #{type} AND c.project_id = #{projectId} AND c.parent_id = #{parentId} AND c.is_delete = FALSE " +
            "UNION " +
            "SELECT e.id, e.project_id, e.catalog_id AS parent_id, e.type, e.name AS name, false as isCatalog, CONCAT( 'e-', e.id ) `key`, e.priority, e.edit_status, e.sort, e.created_at FROM pt_test_script AS e WHERE e.type = #{type} AND e.project_id = #{projectId} AND e.catalog_id = #{parentId} <when test='type!=null and type.ordinal()!=0'> AND type = #{type} </when> AND e.is_delete = FALSE " +
            "ORDER BY isCatalog DESC, sort ASC, created_at ASC" +
            "</script>")
    List<FullTree> listTestScriptTree(@Param("type") CatalogType type, @Param("projectId") Long projectId, @Param("parentId") Long parentId);

    /**
     * 查询某个type 下的 TestCase tree 的所有数据
     * @param catalogType 分类的type，test_case-用例、test_suite-套件 他两分类 type 都是 CatalogType.TEST_CASE
     * @param elementType 元素的type
     * @param caseType case类型
     * @param projectId 项目ID
     * @param parentId tree 节点 ID
     * @return
     */
    @Select("<script>" +
            "SELECT c.id, c.project_id, c.parent_id, c.type, c.name AS name, true as isCatalog, CONCAT( 'c-', c.id ) `key`, null as priority, null as edit_status, c.sort, c.created_at FROM ft_catalog AS c WHERE c.type = #{cType} AND c.project_id = #{projectId} AND c.parent_id = #{parentId} AND c.is_delete = FALSE " +
            "UNION " +
            "SELECT e.id, e.project_id, e.catalog_id AS parent_id, e.type, e.name AS name, false as isCatalog, CONCAT( 'e-', e.id ) `key`, e.priority, e.edit_status, e.sort, e.created_at FROM ft_test_case AS e WHERE e.type = #{eType} AND e.project_id = #{projectId} AND e.catalog_id = #{parentId} <when test='caseType!=null and caseType.ordinal()!=0'> AND case_type = #{caseType} </when> AND e.is_delete = FALSE " +
            "ORDER BY isCatalog DESC, sort ASC, created_at ASC" +
            "</script>")
    List<FullTree> listTestCaseTree(@Param("cType") CatalogType catalogType, @Param("eType") CatalogType elementType, @Param("caseType") CatalogType caseType, @Param("projectId") Long projectId, @Param("parentId") Long parentId);

    /**
     * 查询某个type 下的 Api tree 的所有数据
     * @param catalogType 分类的type，test_case-用例、test_suite-套件 他两分类 type 都是 CatalogType.TEST_CASE
     * @param elementType 元素的type
     * @param caseType case类型
     * @param projectId 项目ID
     * @param parentId tree 节点 ID
     * @return
     */
    @Select("<script>" +
            "SELECT c.id, c.project_id, c.parent_id, c.type, c.name AS name, true as isCatalog, CONCAT( 'c-', c.id ) `key`, c.sort, c.created_at FROM ft_catalog AS c WHERE c.type = #{cType} AND c.project_id = #{projectId} AND c.parent_id = #{parentId} AND c.is_delete = FALSE " +
            "UNION " +
            "SELECT e.id, e.project_id, e.catalog_id AS parent_id, e.type, e.name AS name, false as isCatalog, CONCAT( 'e-', e.id ) `key`, e.sort, e.created_at FROM ft_api AS e WHERE e.type = #{eType} AND e.project_id = #{projectId} AND e.catalog_id = #{parentId} <when test='caseType!=null and caseType.ordinal()!=0'> AND case_type = #{caseType} </when> AND e.is_delete = FALSE " +
            "ORDER BY isCatalog DESC, sort ASC, created_at ASC" +
            "</script>")
    List<FullTree> listApiTree(@Param("cType") CatalogType catalogType, @Param("eType") CatalogType elementType, @Param("caseType") CatalogType caseType, @Param("projectId") Long projectId, @Param("parentId") Long parentId);

    /**
     * 查询某个type 下的 TestCase tree 的所有数据
     * @param catalogType 分类的type，test_case-用例、test_suite-套件 他两分类 type 都是 CatalogType.TEST_CASE
     * @param elementType 元素的type
     * @param caseType case类型
     * @param projectId 项目ID
     * @param parentId tree 节点 ID
     * @return
     */
    @Select("SELECT c.id, c.project_id, c.parent_id, c.type, c.name AS name, true as isCatalog, CONCAT( 'c-', c.id ) `key`, null as priority, null as edit_status, c.sort, c.created_at FROM ft_catalog AS c WHERE c.type = #{cType} AND c.project_id = #{projectId} AND c.parent_id = #{parentId} AND c.is_delete = FALSE " +
            "ORDER BY isCatalog DESC, sort ASC, created_at ASC")
    List<FullTree> listTestCaseTreeCatalog(@Param("cType") CatalogType catalogType, @Param("eType") CatalogType elementType, @Param("caseType") CatalogType caseType, @Param("projectId") Long projectId, @Param("parentId") Long parentId);

    /**
     * 查询某个测试套件下的所有数据（只包括选中的case）
     * @param catalogType 分类的type，test_case-用例、test_suite-套件 他两分类 type 都是 CatalogType.TEST_CASE
     * @param elementType 元素的type
     * @param caseType case类型
     * @param projectId 项目ID
     * @param testSuitesId 测试套件ID
     * @param testResultId 测试结果ID
     * @param parentId tree 节点 ID
     * @return
     */
    @Select("<script>" +
            "SELECT c.id,c.project_id,c.parent_id,c.type,c.NAME AS NAME,TRUE AS isCatalog,CONCAT('c-',c.id) `key`,null as priority, null as edit_status,c.sort,c.created_at,'catalog' AS icon FROM ft_catalog AS c WHERE c.type=#{cType} AND c.project_id=#{projectId} AND c.parent_id=#{parentId} AND c.is_delete=FALSE UNION " +
            "SELECT e.id,e.project_id,e.catalog_id AS parent_id,e.type,e.NAME AS NAME,FALSE AS isCatalog,CONCAT('e-',e.id) `key`,e.priority,e.edit_status,e.sort,e.created_at,COALESCE (CASE WHEN STATUS=22 THEN 'fail' WHEN STATUS=4 THEN 'pass' WHEN STATUS=12 THEN 'stop' END,'wait') AS icon FROM (" +
            "SELECT ftc.* FROM (" +
            "SELECT * FROM ft_test_case WHERE type=#{eType} AND project_id=#{projectId} AND catalog_id=#{parentId} <when test='caseType!=null and caseType.ordinal()!=0'> AND case_type = #{caseType} </when> AND is_delete=FALSE) AS ftc INNER JOIN (" +
            "SELECT * FROM ft_test_suite_test_case WHERE test_suites_id=#{testSuitesId} AND is_delete=FALSE) AS aa ON ftc.id=aa.test_cases_id) AS e LEFT JOIN (" +
            "SELECT `status`,cid FROM ft_test_result_case WHERE rid=#{testResultId}) AS ftrc ON e.id=ftrc.cid ORDER BY isCatalog DESC,sort ASC,created_at ASC" +
            "</script>")
    List<FullTree> listTestSuiteCheckedTestCaseTree(@Param("cType") CatalogType catalogType, @Param("eType") CatalogType elementType, @Param("caseType") CatalogType caseType, @Param("projectId") Long projectId, @Param("testSuitesId") Long testSuitesId, @Param("testResultId") Long testResultId, @Param("parentId") Long parentId);

    /**
     * 查询某个评审计划下的所有数据（只包括选中的case）
     * @param catalogType 分类的 type，test_case-用例、test_suite-套件 他两分类 type 都是 CatalogType.TEST_CASE
     * @param elementType 元素的 type
     * @param caseType case 类型
     * @param projectId 项目 ID
     * @param reviewsId 评审计划 ID
     * @param parentId tree 节点 ID
     * @return
     */
    @Select("<script>" +
            "SELECT c.id,c.project_id,c.parent_id,c.type,c.NAME AS NAME,TRUE AS isCatalog,CONCAT('c-',c.id) `key`,null as priority, null as edit_status,c.sort,c.created_at,'catalog' AS icon FROM ft_catalog AS c WHERE c.type=#{cType} AND c.project_id=#{projectId} AND c.parent_id=#{parentId} AND c.is_delete=FALSE UNION " +
            "SELECT e.id,e.project_id,e.catalog_id AS parent_id,e.type,e.NAME AS NAME,FALSE AS isCatalog,CONCAT('e-',e.id) `key`,e.priority,e.edit_status,e.sort,e.created_at, COALESCE (CASE WHEN STATUS=3 THEN 'fail' WHEN STATUS=4 THEN 'pass' END,'element') AS icon FROM (" +
            "SELECT ftc.* FROM (" +
            "SELECT * FROM ft_test_case WHERE type=#{eType} AND project_id=#{projectId} AND catalog_id=#{parentId} <when test='caseType!=null and caseType.ordinal()!=0'> AND case_type = #{caseType} </when> AND is_delete=FALSE) AS ftc INNER JOIN (" +
            "SELECT * FROM ft_reviews_test_case WHERE reviews_id=#{reviewsId} AND is_delete=FALSE) AS aa ON ftc.id=aa.test_case_id) AS e LEFT JOIN (" +
            "SELECT `status`,test_case_id FROM ft_reviews_result WHERE reviews_id=#{reviewsId} AND is_delete=false) AS frr ON e.id=frr.test_case_id ORDER BY isCatalog DESC,sort ASC,created_at ASC" +
            "</script>")
    List<FullTree> listReviewsCheckedTestCaseTree(@Param("cType") CatalogType catalogType, @Param("eType") CatalogType elementType, @Param("caseType") CatalogType caseType, @Param("projectId") Long projectId, @Param("reviewsId") Long reviewsId, @Param("parentId") Long parentId);

    /**
     * 根据元素 id，查询一个 tree 上的 element
     * @param elementTableName 元素表名称（interface-接口、test_case-用例、test_suite-套件）
     * @param id 元素 id
     * @return
     */
    @Select("SELECT e.id, e.project_id, e.catalog_id AS parent_id, e.name AS `name`, FALSE AS isCatalog, CONCAT( 'e-', e.id ) `key` FROM ${eTableName} AS e WHERE e.id = #{id} AND e.is_delete = FALSE")
    Tree getElement(@Param("eTableName") String elementTableName, @Param("id") Long id);

    /**
     * 根据一个分类元素 id，递归查找父分类列表
     * 例如：一个用例A他在分类A下，分类A又在分类B下。那返回就是 [分类A, 分类B]
     * @param id 元素 id
     * @return
     */
    @Select("SELECT" +
            "   c.id, c.project_id, c.parent_id, c.name, TRUE AS isCatalog, CONCAT( 'c-', c.id ) `key` \n" +
            "FROM" +
            "   ( SELECT @a AS ida, ( SELECT @a := parent_id FROM ft_catalog WHERE id = ida ) AS parent_id FROM ( SELECT @a := #{id} ) temps, ft_catalog WHERE @a <> 0 ) AS tc" +
            "   LEFT JOIN ft_catalog AS c " +
            "ON" +
            "   c.id = tc.ida " +
            "WHERE" +
            "   c.is_delete = FALSE")
    List<Tree> getElementParent(@Param("id") Long id);

    /**
     * 排序 改变后，批量更新 目录 数据
     * @param trees tree list
     * @return
     */
    @Update("<script>" +
            "<foreach collection='list' item='item' separator=';'> " +
                "UPDATE ft_catalog SET parent_id=#{item.parentId}, sort=#{item.sort} WHERE id=#{item.id} AND is_delete=false" +
            "</foreach> " +
            "</script>"
    )
    Integer sortCatalog(@Param("list") List<Tree> trees);

    /**
     * 排序 改变后，批量更新 元素 数据
     * @param eTableName 元素表名称（interface-接口、test_case-用例、test_suite-套件）
     * @param trees tree list
     * @return
     */
    @Update("<script>" +
            "<foreach collection='list' item='item' separator=';'> " +
            "UPDATE ${eTableName} SET catalog_id=#{item.parentId}, sort=#{item.sort} WHERE id=#{item.id} AND is_delete=false" +
            "</foreach> " +
            "</script>"
    )
    Integer sortElement(@Param("eTableName") String eTableName, @Param("list") List<Tree> trees);

    /**
     * 返回某个 父节点 下的所有子目录
     * @param catalogType 分类的type，test_case-用例、test_suite-套件 他两分类 type 都是 CatalogType.TEST_CASE
     * @param parentId 父节点 id
     * @return
     */
    @Select("SELECT c.id, c.project_id, c.parent_id, c.name AS `name`, TRUE AS isCatalog, CONCAT( 'c-', c.id ) `key` FROM ft_catalog AS c WHERE c.type = #{cType} AND c.project_id = #{projectId} AND c.parent_id = #{parentId} AND is_delete=false ORDER BY sort ASC, created_at ASC")
    List<Tree> listSubCatalogs(@Param("cType") CatalogType catalogType, @Param("projectId") Long projectId, @Param("parentId") Long parentId);

    /**
     * 返回某个 父节点 下的所有子元素
     * @param eTableName 元素表名称（interface-接口、test_case-用例、test_suite-套件）
     * @param catalogType 分类的type，test_case-用例、test_suite-套件 他两分类 type 都是 CatalogType.TEST_CASE
     * @param parentId 父节点 id
     * @return
     */
    @Select("SELECT e.id, e.project_id, e.catalog_id AS parent_id, e.name AS `name`, FALSE AS isCatalog, CONCAT( 'e-', e.id ) `key` FROM ${eTableName} AS e WHERE e.type = #{cType} AND e.project_id = #{projectId} AND e.catalog_id = #{parentId} AND e.is_delete = FALSE ORDER BY sort ASC, created_at ASC")
    List<Tree> listSubElements(@Param("eTableName") String eTableName, @Param("cType") CatalogType catalogType, @Param("projectId") Long projectId, @Param("parentId") Long parentId);
}
